{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# members.csv：用户元数据信息\n",
    "\n",
    "msno：用户id   <font color='#ff0000'> 和训练数据中的用户id一致</font>\n",
    "\n",
    "city：城市\n",
    "\n",
    "bd: 年龄。注意：年龄数据有离群点\n",
    "\n",
    "gender：性别\n",
    "\n",
    "registered_via: 注册方式\n",
    "\n",
    "registration_init_time: 注册时间，格式为%Y%m%d\n",
    "\n",
    "expiration_date: 到期时间，格式为 %Y%m%d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>msno</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>gender</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>registration_init_time</th>\n",
       "      <th>expiration_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>XQxgAYj3klVKjR3oxPPXYYFp4soD4TuBghkhMTD4oTw=</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>20110820</td>\n",
       "      <td>20170920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>UizsfmJb9mV54qE9hCYyU07Va97c0lCRLEQX3ae+ztM=</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>20150628</td>\n",
       "      <td>20170622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>D8nEhsIOBSoE6VthTaqDX8U6lqjJ7dLdr72mOyLya2A=</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>20160411</td>\n",
       "      <td>20170712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>mCuD+tZ1hERA/o5GPqk38e041J8ZsBaLcu7nGoIIvhI=</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>20150906</td>\n",
       "      <td>20150907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>q4HRBfVSssAFS9iRfxWrohxuk9kCYMKjHOEagUMV6rQ=</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>20170126</td>\n",
       "      <td>20170613</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           msno  city  bd gender  \\\n",
       "0  XQxgAYj3klVKjR3oxPPXYYFp4soD4TuBghkhMTD4oTw=     1   0    NaN   \n",
       "1  UizsfmJb9mV54qE9hCYyU07Va97c0lCRLEQX3ae+ztM=     1   0    NaN   \n",
       "2  D8nEhsIOBSoE6VthTaqDX8U6lqjJ7dLdr72mOyLya2A=     1   0    NaN   \n",
       "3  mCuD+tZ1hERA/o5GPqk38e041J8ZsBaLcu7nGoIIvhI=     1   0    NaN   \n",
       "4  q4HRBfVSssAFS9iRfxWrohxuk9kCYMKjHOEagUMV6rQ=     1   0    NaN   \n",
       "\n",
       "   registered_via  registration_init_time  expiration_date  \n",
       "0               7                20110820         20170920  \n",
       "1               7                20150628         20170622  \n",
       "2               4                20160411         20170712  \n",
       "3               9                20150906         20150907  \n",
       "4               4                20170126         20170613  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path = 'data/'\n",
    "members = pd.read_csv(path + 'members.csv')\n",
    "members.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 34403 entries, 0 to 34402\n",
      "Data columns (total 7 columns):\n",
      "msno                      34403 non-null object\n",
      "city                      34403 non-null int64\n",
      "bd                        34403 non-null int64\n",
      "gender                    14501 non-null object\n",
      "registered_via            34403 non-null int64\n",
      "registration_init_time    34403 non-null int64\n",
      "expiration_date           34403 non-null int64\n",
      "dtypes: int64(5), object(2)\n",
      "memory usage: 1.8+ MB\n"
     ]
    }
   ],
   "source": [
    "members.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## gender有大量缺失值,其他无缺失值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "msno 34403\n",
      "city 21\n",
      "bd 95\n",
      "gender 3\n",
      "registered_via 6\n"
     ]
    }
   ],
   "source": [
    "values = ['msno','city','bd','gender','registered_via']\n",
    "for i in values:\n",
    "    print(i,len(members[i].unique()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 用户信息未重复\n",
    "接下来对其他进行具体查看"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "city ：\n",
      "1     19445\n",
      "13     3395\n",
      "5      2634\n",
      "4      1732\n",
      "15     1525\n",
      "22     1467\n",
      "6       913\n",
      "14      708\n",
      "12      491\n",
      "9       309\n",
      "8       289\n",
      "11      285\n",
      "18      259\n",
      "10      216\n",
      "21      213\n",
      "3       204\n",
      "17      152\n",
      "7        93\n",
      "16       35\n",
      "20       27\n",
      "19       11\n",
      "Name: city, dtype: int64\n",
      "bd ：\n",
      " 0       19932\n",
      " 22        751\n",
      " 27        750\n",
      " 24        740\n",
      " 26        719\n",
      " 25        716\n",
      " 23        712\n",
      " 28        688\n",
      " 21        685\n",
      " 29        661\n",
      " 20        631\n",
      " 30        602\n",
      " 19        507\n",
      " 31        491\n",
      " 32        466\n",
      " 18        466\n",
      " 33        416\n",
      " 34        404\n",
      " 17        398\n",
      " 35        380\n",
      " 36        341\n",
      " 37        300\n",
      " 38        294\n",
      " 39        226\n",
      " 16        215\n",
      " 40        204\n",
      " 41        194\n",
      " 44        138\n",
      " 42        131\n",
      " 43        121\n",
      "         ...  \n",
      " 102         2\n",
      " 131         1\n",
      " 78          1\n",
      " 85          1\n",
      " 3           1\n",
      " 2           1\n",
      " 1051        1\n",
      " 97          1\n",
      " 144         1\n",
      " 93          1\n",
      " 96          1\n",
      "-43          1\n",
      " 82          1\n",
      " 931         1\n",
      " 106         1\n",
      " 76          1\n",
      " 87          1\n",
      " 101         1\n",
      " 90          1\n",
      " 70          1\n",
      " 1030        1\n",
      " 7           1\n",
      " 12          1\n",
      " 103         1\n",
      "-38          1\n",
      " 89          1\n",
      " 107         1\n",
      " 10          1\n",
      " 11          1\n",
      " 95          1\n",
      "Name: bd, Length: 95, dtype: int64\n",
      "gender ：\n",
      "male      7405\n",
      "female    7096\n",
      "Name: gender, dtype: int64\n",
      "registered_via ：\n",
      "4     11392\n",
      "7      9433\n",
      "9      8628\n",
      "3      4879\n",
      "13       70\n",
      "16        1\n",
      "Name: registered_via, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "values = ['city','bd','gender','registered_via']\n",
    "for i in values:\n",
    "    print(i,'：')\n",
    "    print(members[i].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 考虑到采用LightGBM,则不使用onehot编码\n",
    "### 1. 21类城市，不处理\n",
    "### 2. 年龄有大量的缺失值显示为0，其次存在离群点：负数/超高年龄段/超低年龄段，需要处理\n",
    "### 3. 性别有一半的缺失值，不处理或者舍弃\n",
    "### 4. 注册方式6类，有1类是1，可以删除可以保留，不处理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 处理年龄bd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\15067\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n",
      "C:\\Users\\15067\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    }
   ],
   "source": [
    "bd_new=members['bd']\n",
    "bd_new[bd_new >= 80] = 0\n",
    "bd_new[bd_new <= 1] = 0\n",
    "bd_new= bd_new.replace(0,np.NaN)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 将小于1岁和大于80岁的年龄置为0，后将0置为NaN\n",
    "考虑到小于0岁的肯定是脏数据，而大于80岁的真实数据不多，大部分年龄段是集中在20到30岁之间"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22.0    751\n",
       "27.0    750\n",
       "24.0    740\n",
       "26.0    719\n",
       "25.0    716\n",
       "23.0    712\n",
       "28.0    688\n",
       "21.0    685\n",
       "29.0    661\n",
       "20.0    631\n",
       "30.0    602\n",
       "19.0    507\n",
       "31.0    491\n",
       "32.0    466\n",
       "18.0    466\n",
       "33.0    416\n",
       "34.0    404\n",
       "17.0    398\n",
       "35.0    380\n",
       "36.0    341\n",
       "37.0    300\n",
       "38.0    294\n",
       "39.0    226\n",
       "16.0    215\n",
       "40.0    204\n",
       "41.0    194\n",
       "44.0    138\n",
       "42.0    131\n",
       "43.0    121\n",
       "46.0    115\n",
       "       ... \n",
       "53.0     44\n",
       "54.0     41\n",
       "55.0     33\n",
       "56.0     24\n",
       "57.0     23\n",
       "58.0     20\n",
       "59.0     16\n",
       "61.0     14\n",
       "60.0     13\n",
       "66.0      9\n",
       "62.0      9\n",
       "67.0      9\n",
       "64.0      8\n",
       "65.0      7\n",
       "63.0      7\n",
       "13.0      5\n",
       "68.0      5\n",
       "73.0      4\n",
       "72.0      3\n",
       "5.0       2\n",
       "74.0      2\n",
       "7.0       1\n",
       "76.0      1\n",
       "70.0      1\n",
       "10.0      1\n",
       "12.0      1\n",
       "3.0       1\n",
       "2.0       1\n",
       "11.0      1\n",
       "78.0      1\n",
       "Name: bd, Length: 69, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bd_new.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  将1岁以下（包括负数），以及80岁以上的数据都设置为空数据，因为如果是0的话会有歧义\n",
    "### 处理后年龄段分布在 1-80岁之间"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 不处理性别Gender"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "gender_new=members['gender']\n",
    "#gender_new = gender_new.fillna('other')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 注册时间和过期时间两个特征转化成1个，表示到期时间与注册时间相差天数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "from datetime import timedelta\n",
    "\n",
    "members['registration_init_time'] = pd.to_datetime(members['registration_init_time'], format='%Y%m%d')\n",
    "members['expiration_date'] = pd.to_datetime(members['expiration_date'], format='%Y%m%d')\n",
    "members['registration_expiration_days'] =  members['expiration_date'] - members['registration_init_time']\n",
    "members['registration_expiration_days'] = members['registration_expiration_days'].dt.days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>msno</th>\n",
       "      <th>bd</th>\n",
       "      <th>city</th>\n",
       "      <th>gender</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>registration_expiration_days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>XQxgAYj3klVKjR3oxPPXYYFp4soD4TuBghkhMTD4oTw=</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>2223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>UizsfmJb9mV54qE9hCYyU07Va97c0lCRLEQX3ae+ztM=</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>D8nEhsIOBSoE6VthTaqDX8U6lqjJ7dLdr72mOyLya2A=</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>mCuD+tZ1hERA/o5GPqk38e041J8ZsBaLcu7nGoIIvhI=</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>q4HRBfVSssAFS9iRfxWrohxuk9kCYMKjHOEagUMV6rQ=</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>138</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           msno  bd  city gender  \\\n",
       "0  XQxgAYj3klVKjR3oxPPXYYFp4soD4TuBghkhMTD4oTw= NaN     1    NaN   \n",
       "1  UizsfmJb9mV54qE9hCYyU07Va97c0lCRLEQX3ae+ztM= NaN     1    NaN   \n",
       "2  D8nEhsIOBSoE6VthTaqDX8U6lqjJ7dLdr72mOyLya2A= NaN     1    NaN   \n",
       "3  mCuD+tZ1hERA/o5GPqk38e041J8ZsBaLcu7nGoIIvhI= NaN     1    NaN   \n",
       "4  q4HRBfVSssAFS9iRfxWrohxuk9kCYMKjHOEagUMV6rQ= NaN     1    NaN   \n",
       "\n",
       "   registered_via  registration_expiration_days  \n",
       "0               7                          2223  \n",
       "1               7                           725  \n",
       "2               4                           457  \n",
       "3               9                             1  \n",
       "4               4                           138  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "member_info = pd.concat([members['msno'],bd_new,members['city'],members['gender'],members['registered_via'],members['registration_expiration_days']], axis=1)\n",
    "member_info.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 34403 entries, 0 to 34402\n",
      "Data columns (total 6 columns):\n",
      "msno                            34403 non-null object\n",
      "bd                              14436 non-null float64\n",
      "city                            34403 non-null int64\n",
      "gender                          14501 non-null object\n",
      "registered_via                  34403 non-null int64\n",
      "registration_expiration_days    34403 non-null int64\n",
      "dtypes: float64(1), int64(3), object(2)\n",
      "memory usage: 1.6+ MB\n"
     ]
    }
   ],
   "source": [
    "member_info.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(34403, 6)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "member_info.to_csv(path+'member_info_toLightGBM.csv', index=False)\n",
    "member_info.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 将处理完的数据保存\n",
    "msno：未处理\n",
    "\n",
    "city：未处理\n",
    "\n",
    "bd: 将1岁以下（包括负数），以及80岁以上的数据都设置为空数据\n",
    "\n",
    "gender：缺失值未处理\n",
    "\n",
    "registered_via:未处理\n",
    "\n",
    "registration_init_time，expiration_date:相减表示到期时间与注册时间相差天数 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 问题：\n",
    "1. 年龄是否还需要进一步处理？\n",
    "2. 注册时间和过期时间可以怎么处理？"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
